const oracledb = require('oracledb')

const xlsx = require('node-xlsx');
var fs = require('fs');

var moment = require('moment');

const DB_CONFIG = {
  user: 'cgxzcuser', 
  password: 'Tp3D1rBz#cx2024',
  //connectString: 'jdbc:oracle:thin:@172.50.100.181:1521:cgxzc' // 数据库地址：{IP:PORT/数据库名称}
  connectString: '172.50.100.181:1521/cgxzc' // 数据库地址：{IP:PORT/数据库名称}
}


console.log('运行模式:', oracledb.thin ? 'Thin' : 'Thick');

//oracledb.initOracleClient();
oracledb.initOracleClient({libDir: "C:\\Users\\15371\\scoop\\apps\\oracle-instant-client\\current"});

const 老产销中间表内贸主档 = `
   select * from   SO_MAIN_OC@TGCXDB_105 
`
const 老产销中间表内贸主档where = `
where id in (
         SELECT id FROM (
                         SELECT id,status,  order_sts,  older_order_no,operation_type,
                                ROW_NUMBER() OVER (PARTITION BY older_order_no ORDER BY update_date DESC,operation_date DESC) AS rn
                         FROM SO_MAIN_OC@TGCXDB_105
                         WHERE status = '3'
                           and SUPPLY_UNIT = '01' and
                               order_disable = 0
                           AND ORDER_TYPE_CODE not in ('X')
                     ) WHERE rn = 1 and operation_type != 'D'
                         and (order_sts >= '12020301')
   )
`

const 老产销中间表内贸主档fields = [
    "ID",
    "ORDER_NUM",
    "SALE_ORG",
    "PROD_DEPT",
    "SALE_ID",
    
    "CREATE_USER_NO",
    "CREATE_DATE_TIME",
    "COMPANY_CODE",
    "PRICE_FORMULA_MODE",
    "TAX_RATE",
    "ORDER_TYPE_CODE",
    "CURRENCY",
    "ORDER_USER_CODE",
    "ORDER_USER_CNAME",
    "SETTLE_USER_NUM",
    "ORDER_TOT_WT",
    "ORDER_AMT",
    "ORDER_STATUS",
    "PAPER_CONTRACT_NO",
    "ORDER_PAYMENT",
    "ORDER_CLASSIFY",
    "ORDER_MODE",
    "PRODUCE_PLACE",
    "SUPPLY_UNIT",
    "BILL_DATE",
    "MEMO",
    "EXPORT_FLAG",
    "INVOICE_TYPE",
    "FREIGHT_MODE",
    "TRADE_FLAG",

    "GD_PERSON_NO",
    "SETTLE_USER_NAME",
    "SPECIAL_REMARK",
    "OLDER_ORDER_NO",

    "UPDATE_DATE"
];

const 老产销中间表内贸子档 = `
  select *   FROM SO_dtl_OC@TGCXDB_105 sd 
`

const 老产销中间表内贸子档where = `
where id in (
  SELECT r1.id FROM (
                                         SELECT  sd.id,sd.order_item_num, sd.status, sd.company_code, sd.operation_type, sd.order_sts , sd.order_disable,
                                                 ROW_NUMBER() OVER (PARTITION BY sd.order_item_num ORDER BY sd.update_date DESC,sd.operation_date DESC) AS sd_rn
                                         FROM SO_dtl_OC@TGCXDB_105 sd
                                                  inner JOIN (
                                             SELECT * FROM (
                                                               SELECT status,  order_sts,  older_order_no, operation_type,
                                                                      ROW_NUMBER() OVER (PARTITION BY older_order_no ORDER BY update_date DESC,operation_date DESC) AS rn
                                                               FROM SO_MAIN_OC@TGCXDB_105 WHERE status = '3' and SUPPLY_UNIT = '01' and order_disable = 0 AND ORDER_TYPE_CODE not in ('X')
                                                           ) WHERE rn = 1  and operation_type != 'D' and (order_sts >= '12020301')
                                         ) smo ON smo.OLDER_ORDER_NO  = sd.ORDER_NUM
                                         WHERE sd.status = '3'
                                     ) r1
WHERE  sd_rn = 1 AND r1.operation_type != 'D' and (r1.order_sts >= '12020301')
  )
`

const 老产销中间表内贸子档fields = [
    "ID",
    "ORDER_ITEM_NUM",
    "ORDER_NUM",
    "CREATE_USER_NO",
    "CREATE_DATE_TIME",
    "UPDATE_USER_NO",
    
    "SHOP_SIGN",
    "ORDER_ITEM_STATUS",
    "PSC",
    "PSC_DESC",
    "APN",
    "MSC",
    "FIN_USER_NUM",
    "FIN_USER_NAME",
    "GOOD_STATUS",
    "HEAT_TREAT_METHOD",
    "THICK",
    "ORDER_SHORT_RATE",
    "ORDER_UNIT_CODE",
    "SCHEDULE_MONTH",
    "ACT_UNITPRICE",
    "TRNP_MODE_CODE",
    "PROD_STD",
    "ORDER_QTY",
    "LENGTH_MIN",
    "LENGTH_MAX",
   
    "COIL_OUTSIDE_DIM",
    "DIAGRAM_ID",
    "SPEC_CODE",
    "DIM_SPECS",
    "SPEC_DESC",
    "ORDER_ITEM_AMT",
    "STD_SG_CODE",
    "PROD_CODE",
    "PROD_CNAME",
    "MODEL_CODE",
    "LEN_UNIT",
    "SIGL_LEN",
    "LONG_LEN_RATO",
    "ORD_ENSU_TOTL_LEN",
    "DELVRY_EDATE",
    "BALNCE_PRICE",
    "RIBBON_PRICE",
    "KEEP_TYPE",
    "BALNCE_PRICE_ORG",
    "OUTS_BILLET_FL",
    "MPS_FL",
    "MPS_NO",
    "MPS_NO_FILE",
    "MONITOR_FLAG",
    "MAN_INSPECTION",
    "INSPECTION_VALUE",
    "PIC_FD_CHECK",
    "COOPERATE_FLAG",
    "TAG_MEMO",
    "IF_ADD",
    "PROJECT_NO",
    "LARGESS_FLAG",
    "WHOLE_OVER",
    "FORCE_PASS",
    "PAK_PRICE",
    "FREIGHT",
    "EXTRA_PRICE",
    "ENTRUST_PROCESS_PRICE",
    "ORDER_SPEC_UNIT",
    "PIPE_VARY_CODE",
    "QLY_GRADE_NO",
    "PIPE_TYPE",
    "MANU_METHOD",
    "SORT_ID",
    "MODEL_NAME",
    "PROD_NAME",
    "COIL_OUTSIDE_DIM_ENG",
    "THICK_ENG",
    "INDEX_ID",
    "SHORTEST",
    "LONGEST",
    "DELVRY_RANGE_MIN",
    "DELVRY_RANGE_MAX",
    "AVG_LEN_MIN",
    "AVG_LEN_MAX",
    "SITE_NO",
    "SITENO_NAME",
    "SHOP_SIGN_UNIT",
    "QUALITY_USER",
    "BALNCE_PRICE_TAX",
    "STD_CODE",
    "OUTBOUND_CALCULATE",
    "SALE_SG_STD",
    "SALE_PROD_CODE",
    "SALE_PROD_CODE_NAME",
    "STEELMAKING_LINE_NO",
    "STEEL_ROLLING_LINE_NO",
    "HEAT_TREAT_LINE_NO",
    "DRAWING_NO",
    "MATERIAL_DESC",
    "MATERIAL_CODE",
    "PROCESS_REQUIRE",
    "PROCESS_EXPLAIN",
    "SPRAY_PRINT_CONTENT",
    "SPRAY_PRINT_REMARK",
    "STEEL_PRINT_CONTENT",
    "STEEL_PRINT_REMARK",
    "INSIDE_SPRAY_PRINT_CONTENT",
    "INSIDE_SPRAY_PRINT_REMARK",
    "INSIDE_STEEL_PRINT_CONTENT",
    "INSIDE_STEEL_PRINT_REMARK",
    "WARRANTY_LOAD_FLAG",
    "WARRANTY_TYPE",
    "JY",
    "PACK_MODE",
    "TAG_REQUIRE",
    "WARRANTY",
    "PIPE_PRINT",
    "MONITOR_INFO",
    "WARRANTY_NUM",
    "WARRANTY_LANGUAGE",
    "ZBS_SYS",
 
    "COMPANY_CODE",
    "SHRT_STOVE",
    "OUTSOURCE_COUPMAT_FLAG",
    "OUTS_PIPE",
    "ADDRESS",
    "EXTRA_MEMO",
    "ORDER_SPECIAL_DESC",
    "APN_DESC",

];

const 老产销中间表外贸主档 = `
   select * FROM EO_ORDER_MAIN_OLD@TGCXDB_105
`
const  老产销中间表外贸主档where = `
where id in (
     SELECT id FROM (
                         SELECT id,status,  order_sts,  order_num, operation_type,
                                ROW_NUMBER() OVER (PARTITION BY order_num ORDER BY update_date DESC,operation_date DESC) AS rn
                         FROM EO_ORDER_MAIN_OLD@TGCXDB_105 WHERE status = '3' and SUPPLY_UNIT = '01' and (order_type is null or order_type not like 'X%') and  order_disable = 0
                     ) WHERE rn = 1 and operation_type != 'D'   and (order_sts >= '12020301')
   )
`

const 老产销中间表外贸主档fields = [
    "ID",
    "ORDER_NUM",
    "COMPANY_CODE",
    "ORDER_STATUS",

    "PROD_CODE",
    "PROD_DEPT",
    
    "CURRENCY",
    "SETTLE_MODE",
   
    "SETTLE_USER_NUM",
    "SETTLE_USER_NAME",
   
    "SALE_USER_NUM",
    "SALE_USER_NAME",
   
    "SALE_ID",
 
    "READY_DATE",
    
    
    "ORDER_TYPE",
    
    "PROJECT_FLAG",
    "PROJECT_NAME",
    "DEST",
    "PROJECT_NO",
    
    "ORDER_CLASSIFY",
    "PRODUCE_PLACE",
    "SUPPLY_UNIT",
    
    "MPS_FL",
    "MPS_NO",
    
    "GD_PERSON_NO",
    
];

const 老产销中间表外贸子档 = `
   select * FROM eo_order_detail_old@TGCXDB_105 
`
const 老产销中间表外贸子档where = `
where id in (
   SELECT r1.id FROM (
                         SELECT sd.id,sd.order_item_num, sd.status, sd.company_code, sd.operation_type, sd.order_sts , sd.order_disable,sd.order_num,
                                ROW_NUMBER() OVER (PARTITION BY sd.order_item_num ORDER BY sd.update_date DESC,sd.operation_date DESC) AS sd_rn
                         FROM eo_order_detail_old@TGCXDB_105 sd
                         WHERE sd.status = '3'
                     )r1
                         inner JOIN (
    SELECT m.ORDER_NUM FROM (
                                SELECT eomo.status,  eomo.order_sts,  eomo.order_num, eomo.operation_type,
                                       ROW_NUMBER() OVER (PARTITION BY order_num ORDER BY update_date DESC,operation_date DESC) AS rn
                                FROM EO_ORDER_MAIN_OLD@TGCXDB_105 eomo
                                WHERE eomo.status = '3' and eomo.SUPPLY_UNIT = '01' and (eomo.order_type is null or eomo.order_type not like 'X%') and eomo.order_disable = 0
                            ) m WHERE rn = 1  and m.operation_type != 'D' and m.order_sts >= '12020301'
) smo ON smo.ORDER_NUM  = r1.ORDER_NUM
WHERE r1.sd_rn = 1 AND  r1.operation_type != 'D' and r1.order_sts >= '12020301')
`

const 老产销中间表外贸子档fields = [
  
   
    "ORDER_NUM",
    "ORDER_ITEM_STATUS_CODE",
  
    "PC",
    "SPEC1",
    "PROD_STD",
    "SHOPSIGN",
    "APN",
    "DELIVERY_DATE_CHR",
    "TEST_UNITM",
    "THICK",
    "WIDTH",
    "LENGTH_MIN",
    "LENGTH_MAX",
   
    "SPEC_DESC",
    "ORDER_UNIT_CODE",
    "ORDER_WT",
   
    "MSC",
  
    "PROD_CODE",
    "PROD_CNAME",
   
    "GOOD_STATUS",
   
    "MODEL_CODE",
    "MSC_PLINE_DESC",
   
    "SHRT_STOVE",
    "ENDLEN_SPECSTD",
    "ORD_ENSU_TOTL_LEN",
    "DELVRY_DATE",
    "KEEP_TYPE",
    "OUTS_BILLET_FL",
  
    "MAN_INSPECTION",
    "INSPECTION_VALUE",
   
    "COOPERATE_FLAG",
   
    "OUTBOUND_CALCULATE",
    "MONITOR_FLAG",
    "MEMO",
    "PROJECT_NO",
    "WHOLE_OVER",
    
    "PIPE_VARY_CODE",
    "PROD_NAME",
    "QLY_GRADE_NO",
    "MANU_METHOD",
   
    "PIPE_TYPE",
    "LEN_UNIT",
    "INDEX_ID",
    "AVG_LEN_MIN",
    "AVG_LEN_MAX",
    "DIFRNC_LEN_MAX",
    "SHORTEST",
    "LONGEST",
    "SIGL_LEN",
    "DELVRY_RANGE_MIN",
    "DELVRY_RANGE_MAX",
    "ORD_ENSU_NUM",
    "SPEC_CODE",
    "QUALITY_USER",
    "OUTS_PIPE",
    "SHOPSIGN_UNIT",
    "PONO",
    "PIC_ID",
    "ATTECH_SPEC",
    "SALE_SG_STD",
    "SALE_PROD_CODE",
    "SALE_PROD_CODE_NAME",
    "STEELMAKING_LINE_NO",
    "STEEL_ROLLING_LINE_NO",
    "HEAT_TREAT_LINE_NO",
    "MATERIAL_CODE",
    "MATERIAL_DESC",
    "PROCESS_REQUIRE",
    "PROCESS_EXPLAIN",
    "SPRAY_PRINT_CONTENT",
    "SPRAY_PRINT_REMARK",
    "STEEL_PRINT_CONTENT",
    "STEEL_PRINT_REMARK",
    "INSIDE_SPRAY_PRINT_CONTENT",
    "INSIDE_SPRAY_PRINT_REMARK",
    "INSIDE_STEEL_PRINT_CONTENT",
    "INSIDE_STEEL_PRINT_REMARK",
    "JY",
    "WARRANTY_LOAD_FLAG",
    "WARRANTY_TYPE",
    "FINISH_PROD_DELIVERY_DATE",
    "PACK_MODE",
    "TAG_REQUIRE",
    "WARRANTY",
    "PIPE_PRINT",
    "MONITOR_INFO",
    "WARRANTY_LANGUAGE",
    "WARRANTY_NUM",
    "ZBS_SYS",
    "MATERIAL_REVIEW",
    "MATERIAL_REVIEW_TIME",
    "HANDLE_FLAG",
    "INNER_WAL_FLAG",
    "EXTRA_MEMO",
    "ORDER_SPECIAL_DESC",
    "APN_DESC",
    "ORDER_SHORT_RATE",
   
    "SPECIAL_SURFACE_TREATMENT",
    "SPEC_ENTER_TYPE",
    "SALE_SHOP_SIGN",
    "SALE_SHOP_SIGN_NAME",
    "SALE_GOOD_STATUS",
    "SALE_GOOD_STATUS_NAME",
    "FINAL_USER",
    "LARGESS_FLAG",
    "PIPE_LINE_NO"
];

const getColumn = ` where ROWNUM < 2 `

  const date = moment().format('YYYY-MM-DD HH-mm');
  const dateSubFix = `99999-${date}`;
// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();
      
     const 老产销中间表内贸主档Row =  await connection.execute(老产销中间表内贸主档+getColumn)
     const 老产销中间表内贸主档orderFields = 老产销中间表内贸主档Row.metaData.map(s=>s.name);
      const 老产销中间表内贸主档dif = 老产销中间表内贸主档orderFields.filter(item => !老产销中间表内贸主档fields.includes(item));
    //  console.log('老产销中间表内贸主档dif:',老产销中间表内贸主档dif,老产销中间表内贸主档Row.metaData.length)
        const 老产销中间表内贸主档difneedNULLFlag = []
         let tableName= 'SO_MAIN_OC@TGCXDB_105'
      for(var item of new Set(老产销中间表内贸主档dif)){
         const sqlNull = `
         SELECT
              CASE
                  WHEN COUNT(CASE WHEN ${item} IS NULL OR ${item} = '' THEN 1 END) = COUNT(1)
                      THEN '全部为空'
                  ELSE '存在非空值'
                  END AS result
          FROM ${tableName} ${老产销中间表内贸主档where}
        `
        try{
          let result = await connection.execute(sqlNull)
         const item0 =  {
                  column:tableName+'.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            老产销中间表内贸主档difneedNULLFlag.push(item0)
          }

        }catch(e){
            console.log('e:',e)
        }
       
      }

        console.log('老产销中间表内贸主档difneedNULLFlag:',老产销中间表内贸主档difneedNULLFlag.map(s=>s.column).join(','))



       const 老产销中间表内贸子档Row =  await connection.execute(老产销中间表内贸子档+getColumn)
     const 老产销中间表内贸子档orderFields = 老产销中间表内贸子档Row.metaData.map(s=>s.name);
      const 老产销中间表内贸子档dif = 老产销中间表内贸子档orderFields.filter(item => !老产销中间表内贸子档fields.includes(item));
        const 老产销中间表内贸子档difneedNULLFlag = []
          tableName= 'SO_dtl_OC@TGCXDB_105'
      for(var item of new Set(老产销中间表内贸子档dif)){
         const sqlNull = `
         SELECT
              CASE
                  WHEN COUNT(CASE WHEN ${item} IS NULL OR ${item} = '' THEN 1 END) = COUNT(1)
                      THEN '全部为空'
                  ELSE '存在非空值'
                  END AS result
          FROM ${tableName} ${老产销中间表内贸子档where}
        `
        try{
          let result = await connection.execute(sqlNull)
         const item0 =  {
                  column:tableName+'.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            老产销中间表内贸子档difneedNULLFlag.push(item0)
          }

        }catch(e){
           // console.log('e:',e)
        }
       
      }

       console.log('老产销中间表内贸子档difneedNULLFlag:',老产销中间表内贸子档difneedNULLFlag.map(s=>s.column).join(','))


       const 老产销中间表外贸主档Row =  await connection.execute(老产销中间表外贸主档+getColumn)
     const 老产销中间表外贸主档orderFields = 老产销中间表外贸主档Row.metaData.map(s=>s.name);
      const 老产销中间表外贸主档dif = 老产销中间表外贸主档orderFields.filter(item => !老产销中间表外贸主档fields.includes(item));
        const 老产销中间表外贸主档difneedNULLFlag = []
          tableName= 'EO_ORDER_MAIN_OLD@TGCXDB_105'
        //  console.log(' new Set(老产销中间表外贸主档dif):', new Set(老产销中间表外贸主档dif))
      for(var item of new Set(老产销中间表外贸主档dif)){
         const sqlNull = `
         SELECT
              CASE
                  WHEN COUNT(CASE WHEN ${item} IS NULL OR ${item} = '' THEN 1 END) = COUNT(1)
                      THEN '全部为空'
                  ELSE '存在非空值'
                  END AS result
          FROM ${tableName} ${老产销中间表外贸主档where}
        `
        try{
          let result = await connection.execute(sqlNull)
         const item0 =  {
                  column:tableName+'.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            老产销中间表外贸主档difneedNULLFlag.push(item0)
          }

        }catch(e){
            console.log('e:',e)
        }
       
      }

       console.log('老产销中间表外贸主档difneedNULLFlag:',老产销中间表外贸主档difneedNULLFlag.map(s=>s.column).join(','))


       const 老产销中间表外贸子档Row =  await connection.execute(老产销中间表外贸子档+getColumn)
     const 老产销中间表外贸子档orderFields = 老产销中间表外贸子档Row.metaData.map(s=>s.name);
      const 老产销中间表外贸子档dif = 老产销中间表外贸子档orderFields.filter(item => !老产销中间表外贸子档fields.includes(item));
        const 老产销中间表外贸子档difneedNULLFlag = []
          tableName= 'eo_order_detail_old@TGCXDB_105'
      for(var item of new Set(老产销中间表外贸子档dif)){
         const sqlNull = `
         SELECT
              CASE
                  WHEN COUNT(CASE WHEN ${item} IS NULL OR ${item} = '' THEN 1 END) = COUNT(1)
                      THEN '全部为空'
                  ELSE '存在非空值'
                  END AS result
          FROM ${tableName} ${老产销中间表外贸子档where}
        `
        try{
          let result = await connection.execute(sqlNull)
         const item0 =  {
                  column:tableName+'.'+item,
                  size:result.rows[0][0]
                };
          if(result.rows[0][0]=='全部为空'){
            老产销中间表外贸子档difneedNULLFlag.push(item0)
          }

        }catch(e){
            console.log('e:',e)
        }
       
      }

      
     
      
      
       console.log('老产销中间表外贸子档difneedNULLFlag:',老产销中间表外贸子档difneedNULLFlag.map(s=>s.column).join(','))
       // console.log('needNotNULLFlag:',needNotNULLFlag)


    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    console.log(err)
    throw err;
  } finally {
    await pool.close();
  }
}
run()